﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Utility;
using Model;
namespace Service
{
    public class ResultService : IDisposable
    {
        HW_SchoolMergeLogService MergelogService = new HW_SchoolMergeLogService();
        string pquery = @"WITH p(ShoolCode,NewShoolCode) AS(
	SELECT R.SchoolCode,R.NewSchoolCode FROM dbo.HW_SchoolMergeLog R WHERE NewSchoolCode=@school
)";
        #region 公共
        private System.ComponentModel.Component component = new System.ComponentModel.Component();

        private bool disposed = false;

        ~ResultService()
        {
            Dispose(false);
        }

        #endregion
        #region IDisposable 成员

        public void Dispose()
        {
            Dispose(true);
            GC.SuppressFinalize(this);
        }

        private void Dispose(bool disposing)
        {
            if (!this.disposed)
            {

                if (disposing)
                {

                    component.Dispose();
                }

                disposed = true;

            }
        }
        #endregion
          /// <summary>
        /// 功能：通过ID得到HW_Result2013
        /// 创建人：  Wilson 
        /// 创建时间：2013-1-30   
        /// </summary>
        /// <param name="">主键ID</param>
        /// <returns></returns>
        /// 
        public HW_Result2013 Mapper(SqlDataReader reader)
        {
            HW_Result2013 obj = null;
            if (reader.Read())
            {
                obj = new HW_Result2013();
                //obj.TJND6 = (string)reader["TJND"];
                obj.BH = (string)reader["BH"];
                obj.CHECKID = (decimal)reader["CHECKID"];
                obj.SCHOOLBH = (string)reader["SCHOOLBH"];
                obj.GRADEBH = (string)reader["GRADEBH"];
                obj.CLASSBH = (string)reader["CLASSBH"];
                obj.RXSJ = reader["RXSJ"] as string;
                obj.XH = reader["XH"] as string;
                obj.SCHOOL_NAME = reader["SCHOOL_NAME"] as string;
                obj.GRADE_NAME = reader["GRADE_NAME"] as string;
                obj.CLASS_NAME = reader["CLASS_NAME"] as string;
                obj.XM = reader["XM"] as string;
                obj.XB = (string)reader["XB"];
                obj.CSRQ = (DateTime)reader["CSRQ"];
                obj.MZ = reader["MZ"] as string;
                obj.ZZMM = reader["ZZMM"] as string;
                obj.SY = reader["SY"] as string;
                obj.TJRQ = reader["TJRQ"] as DateTime?;
                obj.NL = reader["NL"] as int?;
                obj.MARK = reader["MARK"] as string;
                obj.SG =reader["SG"] as decimal?;
                obj.SGDJQ = reader["SGDJQ"] as string;
                obj.SGDJD = reader["SGDJD"] as string;
                obj.SGBJ = reader["SGBJ"] as string;
                obj.TZ = reader["TZ"] as decimal?;
                obj.TZDJQ = reader["TZDJQ"] as string;
                obj.TZDJD = reader["TZDJD"] as string;
                obj.TZBJ = reader["TZBJ"] as string;
                obj.YYPJ = reader["YYPJ"] as string;
                obj.YYPJX = reader["YYPJX"] as string;
                obj.TXPJ = reader["TXPJ"] as string;
                obj.FYPJ = reader["FYPJ"] as string;
                obj.XW =reader["XW"] as decimal?;
                obj.XWDJQ = reader["XWDJQ"] as string;
                obj.XWDJD = reader["XWDJD"] as string;
                obj.XWBJ = reader["XWBJ"] as string;
                obj.FHL = reader["FHL"] as decimal?;
                obj.FHLDJQ = reader["FHLDJQ"] as string;
                obj.FHLDJD = reader["FHLDJD"] as string;
                obj.FHLBJ = reader["FHLBJ"] as string;
                obj.WL = reader["WL"] as decimal?;
                obj.WLDJQ = reader["WLDJQ"] as string;
                obj.WLDJD = reader["WLDJD"] as string;
                obj.WLBJ = reader["WLBJ"] as string;
                obj.MB = reader["MB"] as decimal?;
                obj.MBBJ = reader["MBBJ"] as string;
                obj.SSY = reader["SSY"] as decimal?;
                obj.SSYBJ = reader["SSYBJ"] as string;
                obj.SZY = reader["SZY"] as decimal?;
                obj.SZYBJ = reader["SZYBJ"] as string;
                obj.LSL = reader["LSL"] as decimal?;
                obj.RSL = reader["RSL"] as decimal?;
                obj.QTYB = reader["QTYB"] as string;
                obj.LQG = reader["LQG"] as string;
                obj.RQG = reader["RQG"] as string;
                obj.LSY = reader["LSY"] as string;
                obj.RSY = reader["RSY"] as string;
                obj.BS = reader["BS"] as string;
                obj.TL = reader["TL"] is DBNull ? "" : reader["TL"].ToString();
                obj.XJ = reader["XJ"] as string;
                obj.BB = reader["BB"] as string;
                obj.QSBNUM = reader["QSBNUM"] as int?;
                obj.RQB = reader["RQB"] as int?;
                obj.RQS = reader["RQS"] as int?;
                obj.RQH = reader["RQH"] as int?;
                obj.HQB = reader["HQB"] as int?;
                obj.HQS = reader["HQS"] as int?;
                obj.HQH = reader["HQH"] as int?;
                obj.YZB = reader["YZB"] as string;
                obj.BTT = reader["BTT"] as string;
                obj.XK = reader["XK"] as string;
                obj.JZ = reader["JZ"] as string;
                obj.SZ = reader["SZ"] as string;
                obj.PZ = reader["PZ"] as string;
                obj.XZ = reader["XZ"] as string;
                obj.GP = reader["GP"] as string;
                obj.FEI = reader["FEI"] as string;
                obj.XT = reader["XT"] as string;
                obj.XHDB = reader["XHDB"] as int?;
                obj.FHCL = reader["FHCL"] as string;
                obj.PFB = reader["PFB"] as string;
                obj.ZG = reader["ZG"] as decimal?;
                obj.ZGDJQ = reader["ZGDJQ"] as string;
                obj.ZGDJD = reader["ZGDJD"] as string;
                obj.ZGBJ = reader["ZGBJ"] as string;
                obj.JK = reader["JK"] as decimal?;
                obj.JKDJQ = reader["JKDJQ"] as string;
                obj.JKDJD = reader["JKDJD"] as string;
                obj.JKBJ = reader["JKBJ"] as string;
                obj.GPK = reader["GPK"] as decimal?;
                obj.GPKDJQ = reader["GPKDJQ"] as string;
                obj.GPKDJD = reader["GPKDJD"] as string;
                obj.GPKBJ = reader["GPKBJ"] as string;
                obj.BJL = reader["BJL"] as decimal?;
                obj.GSTJ = reader["GSTJ"] as decimal?;
                obj.JJX = reader["JJX"] as decimal?;
                obj.BMKY = reader["BMKY"] as string;
                obj.SJSR = reader["SJSR"] as string;
                obj.WSM = reader["WSM"] as decimal?;
                obj.WSMDJQ = reader["WSMDJQ"] as string;
                obj.WSMDJD = reader["WSMDJD"] as string;
                obj.WSMBJ = reader["WSMBJ"] as string;
                obj.LDTY = reader["LDTY"] as decimal?;
                obj.LDTYDJQ = reader["LDTYDJQ"] as string;
                obj.LDTYDJD = reader["LDTYDJD"] as string;
                obj.LDTYBJ = reader["LDTYBJ"] as string;
                obj.LL = reader["LL"] as decimal?;
                obj.LLDJQ = reader["LLDJQ"] as string;
                obj.LLDJD = reader["LLDJD"] as string;
                obj.LLBJ = reader["LLBJ"] as string;
                obj.NNL = reader["NNL"] as decimal?;
                obj.NNLDJQ = reader["NNLDJQ"] as string;
                obj.NNLDJD = reader["NNLDJD"] as string;
                obj.NNLBJ = reader["NNLBJ"] as string;
                obj.ZWT = reader["ZWT"] as decimal?;
                obj.ZWTDJQ = reader["ZWTDJQ"] as string;
                obj.ZWTDJD = reader["ZWTDJD"] as string;
                obj.ZWTBJ = reader["ZWTBJ"] as string;
                obj.T1 = reader["T1"] as string;
                obj.T2 = reader["T2"] as string;
                obj.T3 = reader["T3"] as string;
                obj.T4 = reader["T4"] as string;
                obj.T5 = reader["T5"] as string;
                obj.T6 = reader["T6"] as string;
                obj.T7 = reader["T7"] as string;
                obj.T8 = reader["T8"] as string;
                obj.T9 = reader["T9"] as string;
                obj.T10 = reader["T10"] as string;
                obj.SLBH = reader["SLBH"] as string;
                obj.MBPJ = reader["MBPJ"] as string;
                obj.SSYPJ = reader["SSYPJ"] as string;
                obj.SZYPJ = reader["SZYPJ"] as string;
                obj.PXPJ = reader["PXPJ"] is DBNull ? "" : reader["PXPJ"].ToString();
                obj.SLPJ = reader["SLPJ"] as string;
                obj.XJH = reader["XJH"] as string;
                obj.YYPJB = reader["YYPJB"] as string;
                obj.TJSY = reader["TJSY"] as int?;
                obj.MB1 = reader["MB1"] as int?;
                obj.MB2 = reader["MB2"] as int?;
                obj.MB3 = reader["MB3"] as int?;
                obj.TJFS = reader["TJFS"] as int?;
                obj.TJPJ = reader["TJPJ"] as string;
                obj.YYPJH = reader["YYPJH"] as string;
                obj.XMPY = reader["XMPY"] as string;
                obj.HXP = reader["HXP"] as decimal?;
                obj.HXPPJ = reader["HXPPJ"] as string;
                obj.EB = reader["EB"] as string;
                obj.YB = reader["YB"] as string;
                obj.TB = reader["TB"] as string;
                obj.JB = reader["JB"] as string;
                obj.PF = reader["PF"] as string;
                obj.JZX = reader["JZX"] as string;
                obj.LBJ = reader["LBJ"] as string;
                obj.JHJS = reader["JHJS"] as string;
                obj.GBZAM = reader["GBZAM"] as decimal?;
                obj.DHSA = reader["DHSA"] as decimal?;
                obj.DHSZ = reader["DHSZ"] as decimal?;
                obj.GG = reader["GG"] as string;
                obj.BMKT = reader["BMKT"] as string;
                obj.YGHKT = reader["YGHKT"] as string;
                obj.YGEKY = reader["YGEKY"] as string;
                obj.YGEKT = reader["YGEKT"] as string;
                obj.BLOOD = reader["BLOOD"] as string;
                obj.YW = reader["YW"] as decimal?;
                obj.TW = reader["TW"] as decimal?;
                obj.CXBH = reader["CXBH"] as string;
                obj.LJSL = reader["LJSL"] as decimal?;
                obj.RJSL = reader["RJSL"] as decimal?;
                obj.JMY = reader["JMY"] as string;
                obj.WGFB = reader["WGFB"] as string;
                obj.YYB = reader["YYB"] as string;
                obj.PEI = reader["PEI"] as string;
                obj.LSLS = reader["LSLS"] as decimal?;
                obj.RSLS = reader["RSLS"] as decimal?;
                obj.LJSLS = reader["LJSLS"] as decimal?;
                obj.RJSLS = reader["RJSLS"] as decimal?;
                obj.RTL = reader["RTL"] as string;
                obj.WSZQ = reader["WSZQ"] as string;
                obj.RS = reader["RS"] as string;
                obj.BXP = reader["BXP"] as decimal?;
                obj.XXB = reader["XXB"] as decimal?;
                obj.SFZH = reader["SFZH"] as string;
                obj.DH = reader["DH"] as string;
                obj.ADDRESS2 = reader["ADDRESS"] as string;
                obj.RXYFJZS = reader["RXYFJZS"] as string;
                obj.YMA = reader["YMA"] as string;
                obj.YMAJY = reader["YMAJY"] as string;
                obj.YMB = reader["YMB"] as string;
                obj.YMBJY = reader["YMBJY"] as string;
                obj.YMC = reader["YMC"] as string;
                obj.YMCJY = reader["YMCJY"] as string;
                obj.YMD = reader["YMD"] as string;
                obj.YMDJY = reader["YMDJY"] as string;
                obj.YME = reader["YME"] as string;
                obj.YMEJY = reader["YMEJY"] as string;
                obj.YMQT = reader["YMQT"] as string;
                obj.RXHJZS = reader["RXHJZS"] as string;
                obj.JWBS = reader["JWBS"] as string;
                obj.QCQFY = reader["QCQFY"] as int?;
                obj.STUDGUID = reader["STUDGUID"] as string;
                obj.SCHOOLID = reader["SCHOOLID"] as string;
                obj.STUDNUM = reader["STUDNUM"] as string;
                obj.YMF = reader["YMF"] as string;
                obj.YMFJY = reader["YMFJY"] as string;
            }
            return obj;
        }
        public DataTable GetById(string tjnd)
        {            
            string sql = "select * from HW_Result"+tjnd+" where TJND= @PK";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@PK",tjnd)
            };
            
            DataSet ds=SqlHelper.ExecuteDataset(WebConn.connString,CommandType.Text,sql,sp);
            return ds.Tables[0];
        }
        public DataTable GetByStudentId(string tjnd, string studentid)
        {
            string sql = "select * from HW_Result" + tjnd + " where bh= @studentid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@studentid",studentid)
            };

            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            return ds.Tables[0];
        }
        public List<APIStudent> GetAPIStudent(string tjnd, string studentid)
        {
            string sql = "select "+tjnd+ " as tjnd, bh,schoolbh,schoolbh,gradebh,classbh,school_name,grade_name,class_name,xh,xm,xb,csrq,tjrq from HW_Result" + tjnd + " where bh= @studentid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@studentid",studentid)
            };

            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            List<APIStudent> list = new List<APIStudent>();
            APIStudent student = null;
            while (reader.Read())
            {
                student = new APIStudent()
                {
                    //tjnd = reader["tjnd"] as string,
                    BH = reader["bh"].ToString(),
                    schoolbh = reader["schoolbh"].ToString(),
                    gradebh = reader["gradebh"] as string,
                    classbh = reader["classbh"] as string,
                    SchoolName = reader["school_name"] as string,
                    grade_name = reader["school_name"] as string,
                    class_name = reader["class_name"] as string,
                    xh = reader["xh"] as string,
                    Name = reader["xm"] as string,
                    Sex = (reader["xb"] as string)=="男"?"1":"2",
                    Birthday = reader["csrq"] as string,
                    Tydate = reader["tjrq"] as string,
                };
                list.Add(student);
            }
            return list;
        }
        public DataTable GetData(string tjnd,string studentcode)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            string sql = "select * from HW_Result" + tjnd + " where TJND= @tjnd and bh=@studentcode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@studentcode",studentcode)
            };

            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            return ds.Tables[0];
        }
        public DataTable Search(int pageIndex,int pageSize,out int count,string tjnd,string name,string filed,string districtid,string school,bool merge)
        {

            string merges = string.Empty;
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
          
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            int mergecount = 0;
            if (merge&&!string.IsNullOrEmpty(school))
            {
                SqlParameter[] pras = new SqlParameter[]
                {
                    new SqlParameter("@school",school)
                };
                var mergeds = SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, pquery + "select ShoolCode from p", pras);
                mergecount = mergeds.Tables[0].Rows.Count;
                if (mergecount > 0)
                {
                    merges = "(";
                    for (int i = 0; i < mergeds.Tables[0].Rows.Count; i++)
                    {
                        merges += "'" + mergeds.Tables[0].Rows[i][0].ToString() + "',";
                    }
                    merges += "'" + school + "'";
                    merges += ")";
                }
                
                //sb.Append(pquery);
                //ct.Append(pquery);
            }

            ct.Append("select count(*) from  HW_Result" + tjnd + " b where " + filed + " = @name ");
            wh.Append("select top(@pageIndex*@pageSize) checkid from HW_school m inner join HW_result" + tjnd + " n on m.schoollcode=n.schoolbh where " + filed + " = @name ");
            sb.Append("select top(@pageSize) " + tjnd + " as tjnd, checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where " + filed + " = @name ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@name",name),
            };
            sp2 = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            if (!string.IsNullOrEmpty(districtid))
            {
                sb.Append("and (b.schoolbh like @districtid) ");
                wh.Append("and (n.schoolbh like @districtid) ");
                //ct.Append("and (a.districtcode=@districtid) ");
                sp = new SqlParameter[]
                {
                    new SqlParameter("@pageSize",pageSize),
                    new SqlParameter("@pageIndex",pageIndex),
                    new SqlParameter("@districtid",districtid+"%"),
                    new SqlParameter("@name",name),
                };
                sp2 = new SqlParameter[]
                {
                    //new SqlParameter("@districtid",districtid),
                    new SqlParameter("@name",name),
                };

            }
           
            if (!string.IsNullOrEmpty(school))
            {
                if (merge && mergecount>0)
                {
                    wh.Append("and  n.schoolbh in " + merges);
                    sb.Append("and  b.schoolbh in " + merges);
                    ct.Append("and  b.schoolbh in " + merges);
                }
                else
                {
                    wh.Append("and n.schoolbh=@school ");
                    sb.Append("and b.schoolbh=@school ");
                    ct.Append("and b.schoolbh=@school ");
                }
              sp = new SqlParameter[]
            {
                 new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@districtid",districtid+"%"),
                new SqlParameter("@name",name),
                 new SqlParameter("@school",school),
            };
              sp2 = new SqlParameter[]
            {
                new SqlParameter("@districtid",districtid+"%"),
                new SqlParameter("@name",name),
                 new SqlParameter("@school",school),
            };
            }
            wh.Append(" order by checkid");
            sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM (");
            sb.Append(wh);
            sb.Append(") A )");
            sb.Append(" order by checkid");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(),sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable NoPageSearch(out int count, string tjnd, string name, string filed, string districtid, string school, bool merge)
        {
            string merges = string.Empty;
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();

            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            int mergecount = 0;
            if (merge && !string.IsNullOrEmpty(school))
            {
                SqlParameter[] pras = new SqlParameter[]
                {
                    new SqlParameter("@school",school)
                };
                var mergeds = SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, pquery + "select ShoolCode from p", pras);
                mergecount = mergeds.Tables[0].Rows.Count;
                if (mergecount > 0)
                {
                    merges = "(";
                    for (int i = 0; i < mergeds.Tables[0].Rows.Count; i++)
                    {
                        merges += "'" + mergeds.Tables[0].Rows[i][0].ToString() + "',";
                    }
                    merges += "'" + school + "'";
                    merges += ")";
                }

                //sb.Append(pquery);
                //ct.Append(pquery);
            }

            ct.Append("select count(*) from  HW_Result" + tjnd + " b where " + filed + " = @name ");
            //wh.Append("select top(@pageIndex*@pageSize) checkid from HW_school m inner join HW_result" + tjnd + " n on m.schoollcode=n.schoolbh where " + filed + " = @name ");
            sb.Append("select top(10000) " + tjnd + " as tjnd, checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where " + filed + " = @name ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            sp2 = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            if (!string.IsNullOrEmpty(districtid))
            {
                sb.Append("and (b.schoolbh like @districtid) ");
                //wh.Append("and (n.schoolbh like @districtid) ");
                ct.Append("and (b.schoolbh like @districtid) ");
                sp = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid+"%"),
                    new SqlParameter("@name",name),
                };
                sp2 = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid+"%"),
                    new SqlParameter("@name",name),
                };

            }

            if (!string.IsNullOrEmpty(school))
            {
                if (merge && mergecount > 0)
                {
                    //wh.Append("and  n.schoolbh in " + merges);
                    sb.Append("and  b.schoolbh in " + merges);
                    ct.Append("and  b.schoolbh in " + merges);
                }
                else
                {
                    //wh.Append("and n.schoolbh=@school ");
                    sb.Append("and b.schoolbh=@school ");
                    ct.Append("and b.schoolbh=@school ");
                }
                sp = new SqlParameter[]
            {
                 
                new SqlParameter("@districtid",districtid+"%"),
                new SqlParameter("@name",name),
                 new SqlParameter("@school",school),
            };
                sp2 = new SqlParameter[]
            {
                new SqlParameter("@districtid",districtid+"%"),
                new SqlParameter("@name",name),
                 new SqlParameter("@school",school),
            };
            }
            //wh.Append(" order by checkid");
            //sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM (");
            //sb.Append(wh);
            //sb.Append(") A )");
            //sb.Append(" order by checkid");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];

        }
        public DataTable StudentSearch(int pageIndex, int pageSize, out int count, string tjnd, string name, string filed, string districtid, string school)
        {
            string xname = null;
            if (name.StartsWith("a"))
                xname = "m.name";
            else
                xname = "n.name";
            
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            ct.Append("select count(StudentCode) from HW_School a inner join HW_Student" + tjnd + " b on a.schoollcode=b.SchoolNo where " + filed + " like @name ");
            wh.Append("select top(@pageIndex*@pageSize) StudentCode from HW_school m inner join HW_Student" + tjnd + " n on m.schoollcode=n.SchoolNo where " + xname + " like @name ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            sb.Append("select top(@pageSize) StudentCode, CityName,DistrictName, a.name name,ClassName,b.name studentname,sex,BornDate,Nation from HW_School a join HW_Student" + tjnd + " b on a.schoollcode=b.SchoolNo where " + filed + " like @name ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@name",name),
            };
            sp2 = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            if (!string.IsNullOrEmpty(districtid))
            {
                sb.Append("and (a.districtcode=@districtid) ");
                wh.Append("and (m.districtcode=@districtid) ");
                ct.Append("and (a.districtcode=@districtid) ");
                sp = new SqlParameter[]
                {
                    new SqlParameter("@pageSize",pageSize),
                    new SqlParameter("@pageIndex",pageIndex),
                    new SqlParameter("@districtid",districtid),
                    new SqlParameter("@name",name),
                };
                sp2 = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid),
                    new SqlParameter("@name",name),
                };

            }

            if (!string.IsNullOrEmpty(school))
            {
                wh.Append("and m.schoollcode=@school ");
                sb.Append("and a.schoollcode=@school ");
                ct.Append("and a.schoollcode=@school ");
                sp = new SqlParameter[]
            {
                 new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@districtid",districtid),
                new SqlParameter("@name",name),
                 new SqlParameter("@school",school),
            };
                sp2 = new SqlParameter[]
            {
                new SqlParameter("@districtid",districtid),
                new SqlParameter("@name",name),
                 new SqlParameter("@school",school),
            };
            }
            wh.Append(" order by StudentCode");
            sb.Append("and StudentCode not in(");
            sb.Append(wh);
            sb.Append(") ");
            sb.Append("order by StudentCode");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable Search(int pageIndex, int pageSize, out int count, string tjnd,string districtid, string school,bool merge)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            string merges = string.Empty;
            int mergecount = 0;
            if (merge && !string.IsNullOrEmpty(school))
            {
                SqlParameter[] pras = new SqlParameter[]
                {
                    new SqlParameter("@school",school)
                };
                var mergeds = SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, pquery + "select ShoolCode from p",pras);
                mergecount = mergeds.Tables[0].Rows.Count;
                if (mergecount > 0)
                {
                    merges = "(";
                    for (int i = 0; i < mergeds.Tables[0].Rows.Count; i++)
                    {
                        merges += "'"+mergeds.Tables[0].Rows[i][0].ToString() + "',";
                    }
                    merges += "'"+school+"'";
                    merges += ")";
                }
            }
            ct.Append("select count(*) from  HW_Result" + tjnd + " b where 1=1 ");
            wh.Append("select top(@pageIndex*@pageSize) checkid from HW_school m inner join HW_result" + tjnd + " n on m.schoollcode=n.schoolbh where 1=1 ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            //sb.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY keyField DESC) AS rowNum, * FROM tableName) AS t WHERE rowNum > start AND rowNum <= end");
            sb.Append("select top(@pageSize) " + tjnd + " as tjnd,checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a inner join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where 1=1 ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
            };
            sp2 = new SqlParameter[]
            {
            };
            if (!string.IsNullOrEmpty(districtid))
            {
                sb.Append("and (b.schoolbh like @districtid) ");
                wh.Append("and (n.schoolbh like @districtid) ");
                ct.Append("and (b.schoolbh like @districtid) ");
                sp = new SqlParameter[]
                {
                    new SqlParameter("@pageSize",pageSize),
                    new SqlParameter("@pageIndex",pageIndex),
                    new SqlParameter("@districtid",districtid+"%"),
                };
                sp2 = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid+"%"),
                };

            }

            if (!string.IsNullOrEmpty(school))
            {
                if (merge && mergecount>0)
                {
                    wh.Append("and  n.schoolbh in " + merges);
                    sb.Append("and  b.schoolbh in " + merges);
                    ct.Append("and  b.schoolbh in " + merges);
                }
                else
                {
                    wh.Append("and n.schoolbh=@school ");
                    sb.Append("and b.schoolbh=@school ");
                    ct.Append("and b.schoolbh=@school ");
                }
                sp = new SqlParameter[]
                {
                     new SqlParameter("@pageSize",pageSize),
                    new SqlParameter("@pageIndex",pageIndex),
                    new SqlParameter("@districtid",districtid+"%"),
                     new SqlParameter("@school",school),
                };
                sp2 = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid+"%"),
                     new SqlParameter("@school",school),
                };
            }
            wh.Append(" order by checkid");
            sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM (");
            sb.Append(wh);
            sb.Append(") A )");
            sb.Append(" order by checkid");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable NoPageSearch(out int count, string tjnd, string districtid, string school, bool merge)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            string merges = string.Empty;
            int mergecount = 0;
            if (merge && !string.IsNullOrEmpty(school))
            {
                SqlParameter[] pras = new SqlParameter[]
                {
                    new SqlParameter("@school",school)
                };
                var mergeds = SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, pquery + "select ShoolCode from p", pras);
                mergecount = mergeds.Tables[0].Rows.Count;
                if (mergecount > 0)
                {
                    merges = "(";
                    for (int i = 0; i < mergeds.Tables[0].Rows.Count; i++)
                    {
                        merges += "'" + mergeds.Tables[0].Rows[i][0].ToString() + "',";
                    }
                    merges += "'" + school + "'";
                    merges += ")";
                }
            }
            ct.Append("select count(*) from  HW_Result" + tjnd + " b where 1=1 ");
            //wh.Append("select top(@pageIndex*@pageSize) checkid from HW_school m inner join HW_result" + tjnd + " n on m.schoollcode=n.schoolbh where 1=1 ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            //sb.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY keyField DESC) AS rowNum, * FROM tableName) AS t WHERE rowNum > start AND rowNum <= end");
            sb.Append("select top(10000) " + tjnd + " as tjnd,checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a inner join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where 1=1 ");
            sp = new SqlParameter[]
            {
            };
            sp2 = new SqlParameter[]
            {
            };
            if (!string.IsNullOrEmpty(districtid))
            {
                sb.Append("and (b.schoolbh like @districtid) ");
                //wh.Append("and (n.schoolbh like @districtid) ");
                ct.Append("and (b.schoolbh like @districtid) ");
                sp = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid+"%"),
                };
                sp2 = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid+"%"),
                };

            }

            if (!string.IsNullOrEmpty(school))
            {
                if (merge && mergecount > 0)
                {
                    //wh.Append("and  n.schoolbh in " + merges);
                    sb.Append("and  b.schoolbh in " + merges);
                    ct.Append("and  b.schoolbh in " + merges);
                }
                else
                {
                    //wh.Append("and n.schoolbh=@school ");
                    sb.Append("and b.schoolbh=@school ");
                    ct.Append("and b.schoolbh=@school ");
                }
                sp = new SqlParameter[]
                {
                    
                    new SqlParameter("@districtid",districtid+"%"),
                     new SqlParameter("@school",school),
                };
                sp2 = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid+"%"),
                     new SqlParameter("@school",school),
                };
            }
            //wh.Append(" order by checkid");
            //sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM (");
            //sb.Append(wh);
            //sb.Append(") A )");
            //sb.Append(" order by checkid");
            //string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable Search(int pageIndex, int pageSize, out int count, string tjnd)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            ct.Append("select count(bh) from HW_Result" + tjnd + " where 1=1 ");
            wh.Append("select top(@pageIndex*@pageSize) checkid from HW_result" + tjnd + " where 1=1 ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
//            string s = @" SELECT TOP (@pageSize) * FROM  HW_School a inner join HW_Result" + tjnd + @" b on a.schoollcode=b.schoolbh where 1=1 
//                            WHERE checkid >
//                                      (
//                                      SELECT ISNULL(MAX(checkid),0) 
//                                      FROM 
//                                            (
//                                            SELECT TOP @pageSize*@pageIndex checkid FROM HW_School a inner join HW_Result" + tjnd + @" b on a.schoollcode=b.schoolbh where 1=1  ORDER BY id
//                                            ) A
//                                      )
//                            ORDER BY id";
            sb.Append("select top(@pageSize) " + tjnd + " as tjnd, checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a inner join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where 1=1 ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
            };
            sp2 = new SqlParameter[]
            {
            };
            wh.Append(" order by checkid");
            sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM ( ");
            sb.Append(wh);
            sb.Append(") A )");
            sb.Append("order by checkid");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, "select sum(scount) from YearResultCount where syear="+tjnd+"group by syear");
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable NoPageSearch(out int count, string tjnd)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            //StringBuilder wh = new StringBuilder();
            //StringBuilder ct = new StringBuilder();
            //ct.Append("select count(bh) from HW_Result" + tjnd + " where 1=1 ");
            //wh.Append("select top(@pageIndex*@pageSize) checkid from HW_result" + tjnd + " where 1=1 ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            
            sb.Append("select top(10000) " + tjnd + " as tjnd, checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a inner join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where 1=1 ");
            sp = new SqlParameter[]
            {
                
            };
            sp2 = new SqlParameter[]
            {
            };
            //wh.Append(" order by checkid");
            //sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM ( ");
            //sb.Append(wh);
            //sb.Append(") A )");
            //sb.Append("order by checkid");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, "select sum(scount) from YearResultCount where syear=" + tjnd + "group by syear");
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable SearchCondition(int pageIndex, int pageSize, out int count, string tjnd, string name, string field)
        {
            
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            ct.Append("select count(*) from HW_Result" + tjnd + " where 1=1 and " + field + " = @name  ");
            wh.Append("select top(@pageIndex*@pageSize) checkid from HW_result" + tjnd + " where 1=1 and " + field + " = @name  ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            sb.Append("select top(@pageSize) " + tjnd + " as tjnd, checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a inner join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where " + field + " = @name ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@name",name),
            };
            sp2 = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            wh.Append(" order by checkid");
            sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM (");
            sb.Append(wh);
            sb.Append(") A )");
            sb.Append(" order by checkid");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable NoPageSearchCondition(out int count, string tjnd, string name, string field)
        {

            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            ct.Append("select count(*) from HW_Result" + tjnd + " where 1=1 and " + field + " = @name  ");
            //wh.Append("select top(@pageIndex*@pageSize) checkid from HW_result" + tjnd + " where 1=1 and " + field + " = @name  ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            sb.Append("select top(10000) " + tjnd + " as tjnd, checkid, bh,CityName,DistrictName,School_name,Class_Name,XM,XB,CSRQ,MZ,SY,NL from HW_School a inner join HW_Result" + tjnd + " b on a.schoollcode=b.schoolbh where " + field + " = @name ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            sp2 = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            //wh.Append(" order by checkid");
            //sb.Append(" and checkid >( SELECT ISNULL(MAX(checkid),0) FROM (");
            //sb.Append(wh);
            //sb.Append(") A )");
            //sb.Append(" order by checkid");
            //string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }


        public DataTable StudentSearch(int pageIndex, int pageSize, out int count, string tjnd, string districtid, string school)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            ct.Append("select count(StudentCode) from HW_School a inner join HW_Student" + tjnd + " b on a.schoollcode=b.SchoolNo where 1=1 ");
            wh.Append("select top(@pageIndex*@pageSize) StudentCode from HW_school m inner join HW_Student" + tjnd + " n on m.schoollcode=n.SchoolNo where 1=1 ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            sb.Append("select top(@pageSize) StudentCode, CityName,DistrictName, a.name name,ClassName,b.name studentname,sex,BornDate,Nation from HW_School a join HW_Student" + tjnd + " b on a.schoollcode=b.SchoolNo where 1=1 ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
            };
            sp2 = new SqlParameter[]
            {
            };
            if (!string.IsNullOrEmpty(districtid))
            {
                sb.Append("and (a.districtcode=@districtid) ");
                wh.Append("and (m.districtcode=@districtid or m.cityid=@districtid) ");
                ct.Append("and (a.districtcode=@districtid) ");
                sp = new SqlParameter[]
                {
                    new SqlParameter("@pageSize",pageSize),
                    new SqlParameter("@pageIndex",pageIndex),
                    new SqlParameter("@districtid",districtid),
                };
                sp2 = new SqlParameter[]
                {
                    new SqlParameter("@districtid",districtid),
                };

            }

            if (!string.IsNullOrEmpty(school))
            {
                wh.Append("and m.schoollcode=@school ");
                sb.Append("and a.schoollcode=@school ");
                ct.Append("and a.schoollcode=@school ");
                sp = new SqlParameter[]
            {
                 new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@districtid",districtid),
                 new SqlParameter("@school",school),
            };
                sp2 = new SqlParameter[]
            {
                new SqlParameter("@districtid",districtid),
                 new SqlParameter("@school",school),
            };
            }
            wh.Append(" order by StudentCode");
            sb.Append("and StudentCode not in(");
            sb.Append(wh);
            sb.Append(") ");
            sb.Append("order by StudentCode");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable StudentSearch(int pageIndex, int pageSize, out int count, string tjnd)
        {
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            ct.Append("select count(StudentCode) from HW_Student" + tjnd + " where 1=1 ");
            wh.Append("select top(@pageIndex*@pageSize) StudentCode from HW_Student" + tjnd + " where 1=1 ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            sb.Append("select top(@pageSize) StudentCode, CityName,DistrictName, a.name name,ClassName,b.name studentname,sex,BornDate,Nation from HW_Student" + tjnd + " b inner join HW_school a on a.schoollcode=b.SchoolNo where 1=1 ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
            };
            sp2 = new SqlParameter[]
            {
            };
            wh.Append(" order by StudentCode");
            sb.Append("and StudentCode not in(");
            sb.Append(wh);
            sb.Append(") ");
            sb.Append("order by StudentCode");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }
        public DataTable StudentSearchCondition(int pageIndex, int pageSize, out int count, string tjnd, string name, string field)
        {
            string xname = null;
            if (name.StartsWith("a"))
                xname = "m.name";
            else
                xname = "n.name";
            
            StringBuilder sb = new System.Text.StringBuilder();
            StringBuilder wh = new StringBuilder();
            StringBuilder ct = new StringBuilder();
            ct.Append("select count(StudentCode) from HW_School a inner join HW_Student" + tjnd + " b on a.schoollcode=b.SchoolNo where " + field + " like @name ");
            //ct.Append("select count(StudentCode) from HW_Student" + tjnd + " where 1=1 and " + field + " like @name  ");

            wh.Append("select top(@pageIndex*@pageSize) StudentCode from HW_school m inner join HW_Student" + tjnd + " n on m.schoollcode=n.SchoolNo where " + xname + " like @name ");
            //wh.Append("select top(@pageIndex*@pageSize) StudentCode from HW_Student" + tjnd + " where 1=1 and " + field + " like @name  ");
            SqlParameter[] sp = null;
            SqlParameter[] sp2 = null;
            //sb.Append("select * from HW_Result" + tjnd + " where TJND = @PK and name like @name and ");
            sb.Append("select top(@pageSize) StudentCode,CityName,DistrictName, a.name name,ClassName,b.name studentname,sex,BornDate,Nation from HW_School a join HW_Student" + tjnd + " b on a.schoollcode=b.SchoolNo where 1=1 and " + field + " like @name ");

            //sb.Append("select top(@pageSize)  CityName,DistrictName, a.name name,ClassName,b.name studentname,sex,BornDate,Nation from HW_School a join HW_Student" + tjnd + " b on a.schoollcode=b.SchoolNo where " + filed + " like @name ");
            sp = new SqlParameter[]
            {
                new SqlParameter("@pageSize",pageSize),
                new SqlParameter("@pageIndex",pageIndex),
                new SqlParameter("@name",name),
            };
            sp2 = new SqlParameter[]
            {
                new SqlParameter("@name",name),
            };
            wh.Append(" order by StudentCode");
            sb.Append("and StudentCode not in(");
            sb.Append(wh);
            sb.Append(") ");
            sb.Append("order by StudentCode");
            string sql = sb.ToString();
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sb.ToString(), sp);

            object o = SqlHelper.ExecuteScalar(WebConn.connString, CommandType.Text, ct.ToString(), sp2);
            count = Convert.ToInt32(o);
            return ds.Tables[0];
        }

        public HW_Result2013 GetModelStudent(string tjnd, string studentid)
        {
            string sql = "select * from HW_Result" + tjnd + " where bh= @studentid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@studentid",studentid)
            };

            SqlDataReader reader = SqlHelper.ExecuteReader(WebConn.connString, CommandType.Text, sql, sp);
            return Mapper(reader);
        }
        public DataTable GetSelfResult(string tjnd, string studentid)
        {
            string sql = "select * from CustomItem where TJND=@tjnd and BH=@studentid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@studentid",studentid),
            };
            DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }
        public DataTable GetSelfVaccin(string tjnd, string studentid)
        {
            string sql = "select * from CustomVaccin where TJND=@tjnd and BH=@studentid";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@studentid",studentid),
            };
            DataTable dt = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp).Tables[0];
            return dt;
        }
        public void CountYearResult()
        {
            string year = DateTime.Now.Year.ToString();
            string sql = "select count(*) from HW_Result" + year+" where term=1";
            
            using(SqlConnection conn=new SqlConnection (Utility.WebConn.connString))
            {
                try
                {
                    SqlCommand comm = new SqlCommand(sql, conn);
                    comm.CommandTimeout = 60 * 60*30;
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    var o = comm.ExecuteScalar();
                    var ds= SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, "select * from YearResultCount where syear=" + year + " and term=1");
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, "insert into YearResultCount values(" + year + ",1," + o.ToString() + ")");
                    }
                    else
                    {
                        string countsql = "update YearResultCount set scount=" + o.ToString()+" where syear=" + year + " and term=1";
                        SqlHelper.ExecuteNonQuery(Utility.WebConn.connString, CommandType.Text, countsql);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
             using(SqlConnection conn=new SqlConnection (Utility.WebConn.connString))
            {
                 string sql2 = "select count(*) from HW_Result" + year + " where term=2";
                try
                {
                    SqlCommand comm = new SqlCommand(sql, conn);
                    comm.CommandTimeout = 60 * 60 * 30;
                    if (conn.State == ConnectionState.Closed)
                        conn.Open();
                    var o = comm.ExecuteScalar();
                    var ds= SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, "select * from YearResultCount where syear=" + year + " and term=2");
                    if (ds.Tables[0].Rows.Count == 0)
                    {
                        SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, "insert into YearResultCount values(" + year + ",2," + o.ToString() + ")");
                    }
                    else
                    {
                        string countsql = "update YearResultCount set scount="+ o.ToString()+" where syear=" + year + " and term=2";
                        SqlHelper.ExecuteNonQuery(Utility.WebConn.connString, CommandType.Text, countsql);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                finally
                {
                    conn.Close();
                }
            }
        }
        public DataSet GetCountYearResult()
        {
            string sql = "select syear,sum(scount) scount from YearResultCount group by syear";
           var ds= SqlHelper.ExecuteDataset(Utility.WebConn.connString, CommandType.Text, sql);
           return ds;

        }
        public DataSet GetBySchoolCode(string tjnd, string schoolCode)
        {
            string sql = @"select * from HW_Result" + tjnd + " a left join(select * from customItem where tjnd='"+tjnd+@"')b on a.schoolbh=b.schoolcode left join 
            (select * from customVaccin where tjnd='"+tjnd+"') c on a.schoolbh=c.schoolcode  where schoolbh=@schoolCode";
            SqlParameter[] sp = new SqlParameter[]
            {
                new SqlParameter("@tjnd",tjnd),
                new SqlParameter("@schoolCode",schoolCode),
            };
            DataSet ds = SqlHelper.ExecuteDataset(WebConn.connString, CommandType.Text, sql, sp);
            return ds;
        }
    }
}
